#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
污染类型数据导入导出服务
支持Excel和JSON格式的数据导入导出
"""

import pandas as pd
import json
import os
from datetime import datetime
from typing import List, Dict, Any, Optional
from sqlalchemy import text
from models.pollution_type import PollutionType, PollutionTypeExample
from services.pollution_type_service import pollution_type_service
from core.database import get_db_session
from core.logging_config import get_logger

logger = get_logger("pollution_type_import_export")

class PollutionTypeImportExport:
    """污染类型导入导出服务"""
    
    def export_pollution_types_to_excel(self, file_path: str, customer_id: str = "default"):
        """导出污染类型到Excel文件"""
        types = pollution_type_service.get_all_pollution_types(customer_id, active_only=False)

        data = []
        for ptype in types:
            data.append({
                "序号": ptype.get("id"),
                "标签代码": ptype.get("code"),
                "标签名称": ptype.get("name"),
                "标签分类": ptype.get("tag_category"),
                "使用范围": ptype.get("usage_scope"),
                "排除范围": ptype.get("exclusion_scope"),
                "显示名称": ptype.get("display_name"),
                "父级标签代码": ptype.get("parent_tag_code"),
                "父级类型代码": ptype.get("parent_code"),
                "层级": ptype.get("level"),
                "排序": ptype.get("sort_order"),
                "说明": ptype.get("description"),
                "是否激活": "是" if ptype.get("is_active") else "否",
                "是否系统类型": "是" if ptype.get("is_system") else "否"
            })

        df = pd.DataFrame(data)
        df.to_excel(file_path, index=False)
        print(f"已导出 {len(data)} 条污染类型记录到 {file_path}")
        return len(data)
    
    def import_pollution_types_from_excel(self, file_path: str, customer_id: str = "default"):
        """从Excel文件导入污染类型"""
        try:
            df = pd.read_excel(file_path)
            
            success_count = 0
            error_count = 0
            errors = []
            
            for index, row in df.iterrows():
                try:
                    type_data = {
                        "code": str(row.get("标签代码", "")),
                        "name": str(row.get("标签名称", "")),
                        "parent_code": str(row.get("父级类型代码", "")) if pd.notna(row.get("父级类型代码")) and str(row.get("父级类型代码")) != "nan" else None,
                        "level": int(row.get("层级", 1)),
                        "tag_name": str(row.get("标签名称", "")) if pd.notna(row.get("标签名称")) else None,
                        "tag_category": str(row.get("标签分类", "")) if pd.notna(row.get("标签分类")) and str(row.get("标签分类")) != "nan" else None,
                        "usage_scope": str(row.get("使用范围", "")) if pd.notna(row.get("使用范围")) and str(row.get("使用范围")) != "nan" else None,
                        "exclusion_scope": str(row.get("排除范围", "")) if pd.notna(row.get("排除范围")) and str(row.get("排除范围")) != "nan" else None,
                        "display_name": str(row.get("显示名称", "")) if pd.notna(row.get("显示名称")) and str(row.get("显示名称")) != "nan" else None,
                        "parent_tag_code": str(row.get("父级标签代码", "")) if pd.notna(row.get("父级标签代码")) and str(row.get("父级标签代码")) != "nan" else None,
                        "sort_order": int(row.get("排序", 0)),
                        "description": str(row.get("说明", "")) if pd.notna(row.get("说明")) and str(row.get("说明")) != "nan" else None,
                        "is_active": str(row.get("是否激活", "是")) == "是",
                        "is_system": str(row.get("是否系统类型", "否")) == "是"
                    }
                    
                    # 验证必填字段
                    if not type_data["code"] or type_data["code"] == "nan":
                        raise ValueError("标签代码不能为空")
                    if not type_data["name"] or type_data["name"] == "nan":
                        raise ValueError("标签名称不能为空")
                    
                    pollution_type_service.create_pollution_type(customer_id, type_data)
                    success_count += 1
                    
                except Exception as e:
                    error_msg = f"第{index+1}行导入失败: {row.get('标签名称', '未知')} - {str(e)}"
                    errors.append(error_msg)
                    error_count += 1
                    print(error_msg)
            
            result = {
                "success_count": success_count,
                "error_count": error_count,
                "errors": errors
            }
            
            print(f"污染类型导入完成: 成功 {success_count} 条, 失败 {error_count} 条")
            return result
            
        except Exception as e:
            print(f"文件读取失败: {str(e)}")
            raise
    
    def export_examples_to_excel(self, file_path: str, customer_id: str = "default", pollution_type_id: Optional[int] = None):
        """导出污染类型样例到Excel文件"""
        examples = pollution_type_service.get_pollution_type_examples(customer_id, pollution_type_id, active_only=False)

        # 获取所有污染类型ID对应的名称
        type_names = {}
        if examples:
            type_ids = [ex["pollution_type_id"] for ex in examples if ex.get("pollution_type_id")]
            if type_ids:
                with get_db_session() as db:
                    types = db.query(PollutionType).filter(
                        PollutionType.id.in_(type_ids),
                        PollutionType.customer_id == customer_id
                    ).all()
                    type_names = {ptype.id: ptype.name for ptype in types}

        data = []
        for example in examples:
            data.append({
                "序号": example.get("id"),
                "污染类型ID": example.get("pollution_type_id"),
                "污染类型名称": type_names.get(example.get("pollution_type_id"), "未知"),
                "样例内容": example.get("example_content"),
                "判断依据": example.get("judgment_basis"),
                "关键词": example.get("keywords"),
                "置信度": example.get("confidence_level"),
                "是否激活": "是" if example.get("is_active") else "否"
            })

        df = pd.DataFrame(data)
        df.to_excel(file_path, index=False)
        print(f"已导出 {len(data)} 条污染类型样例记录到 {file_path}")
        return len(data)
    
    def import_examples_from_excel(self, file_path: str, customer_id: str = "default"):
        """从Excel文件导入污染类型样例"""
        try:
            df = pd.read_excel(file_path)
            
            success_count = 0
            error_count = 0
            errors = []
            
            for index, row in df.iterrows():
                try:
                    # 获取污染类型ID
                    pollution_type_id = row.get("污染类型ID")
                    if pd.isna(pollution_type_id):
                        # 如果没有ID，尝试通过名称查找
                        type_name = str(row.get("污染类型名称", ""))
                        if type_name and type_name != "nan":
                            with get_db_session() as db:
                                ptype = db.query(PollutionType).filter(
                                    PollutionType.customer_id == customer_id,
                                    PollutionType.name == type_name,
                                    PollutionType.is_active == True
                                ).first()
                                if ptype:
                                    pollution_type_id = ptype.id
                                else:
                                    raise ValueError(f"未找到污染类型: {type_name}")
                        else:
                            raise ValueError("污染类型ID或名称不能为空")
                    
                    example_data = {
                        "example_content": str(row.get("样例内容", "")),
                        "judgment_basis": str(row.get("判断依据", "")) if pd.notna(row.get("判断依据")) and str(row.get("判断依据")) != "nan" else None,
                        "keywords": str(row.get("关键词", "")) if pd.notna(row.get("关键词")) and str(row.get("关键词")) != "nan" else None,
                        "confidence_level": float(row.get("置信度", 0.8)),
                        "is_active": str(row.get("是否激活", "是")) == "是"
                    }
                    
                    # 验证必填字段
                    if not example_data["example_content"] or example_data["example_content"] == "nan":
                        raise ValueError("样例内容不能为空")
                    
                    pollution_type_service.create_pollution_type_example(customer_id, int(pollution_type_id), example_data)
                    success_count += 1
                    
                except Exception as e:
                    error_msg = f"第{index+1}行导入失败: {row.get('样例内容', '未知')[:50]}... - {str(e)}"
                    errors.append(error_msg)
                    error_count += 1
                    print(error_msg)
            
            result = {
                "success_count": success_count,
                "error_count": error_count,
                "errors": errors
            }
            
            print(f"污染类型样例导入完成: 成功 {success_count} 条, 失败 {error_count} 条")
            return result
            
        except Exception as e:
            print(f"文件读取失败: {str(e)}")
            raise
    
    def export_complete_data_to_excel(self, file_path: str, customer_id: str = "default"):
        """导出完整的污染类型数据（包含样例）"""
        import os
        import tempfile

        # 创建临时文件
        temp_types = tempfile.NamedTemporaryFile(suffix='.xlsx', delete=False)
        temp_examples = tempfile.NamedTemporaryFile(suffix='.xlsx', delete=False)

        try:
            with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
                # 导出污染类型
                self.export_pollution_types_to_excel(temp_types.name, customer_id)
                df_types = pd.read_excel(temp_types.name)
                df_types.to_excel(writer, sheet_name='污染类型', index=False)

                # 导出样例
                self.export_examples_to_excel(temp_examples.name, customer_id)
                df_examples = pd.read_excel(temp_examples.name)
                df_examples.to_excel(writer, sheet_name='样例数据', index=False)

            print(f"已导出完整的污染类型数据到 {file_path}")
        finally:
            # 确保删除临时文件
            try:
                if os.path.exists(temp_types.name):
                    os.remove(temp_types.name)
                if os.path.exists(temp_examples.name):
                    os.remove(temp_examples.name)
            except Exception as e:
                print(f"删除临时文件失败: {str(e)}")
    
    def get_import_template(self, template_type: str = "types") -> pd.DataFrame:
        """获取导入模板"""
        if template_type == "types":
            template_data = {
                "标签代码": ["01", "01-01"],
                "标签名称": ["大气污染", "工业废气污染"],
                "父级类型代码": [None, "01"],
                "层级": [1, 2],
                "标签分类": ["", ""],
                "使用范围": ["", ""],
                "排除范围": ["", ""],
                "显示名称": ["", ""],
                "父级标签代码": ["", ""],
                "排序": [0, 0],
                "说明": ["大气环境相关污染", "工业企业排放的废气污染"],
                "是否激活": ["是", "是"],
                "是否系统类型": ["否", "否"]
            }
        elif template_type == "examples":
            template_data = {
                "污染类型ID": [1, 1],
                "污染类型名称": ["大气污染", "大气污染"],
                "样例内容": [
                    "样例内容1：描述污染情况...",
                    "样例内容2：描述另一个污染情况..."
                ],
                "判断依据": [
                    "判断依据说明...",
                    "判断依据说明..."
                ],
                "关键词": [
                    "关键词1,关键词2",
                    "关键词3,关键词4"
                ],
                "置信度": [0.9, 0.8],
                "是否激活": ["是", "是"]
            }
        else:
            raise ValueError("不支持的模板类型")
  
        return pd.DataFrame(template_data)

    def export_pollution_types_to_json(self, file_path: str, customer_id: str = "default") -> int:
        """
        导出污染类型数据到JSON文件

        Args:
            file_path: JSON文件路径
            customer_id: 客户ID，默认为"default"

        Returns:
            导出的记录数量
        """
        try:
            logger.info(f"开始导出污染类型数据到JSON文件: {file_path}")

            # 获取所有污染类型数据（包括非激活的）
            types = pollution_type_service.get_all_pollution_types(customer_id, active_only=False)

            # 转换为可序列化的格式
            export_data = {
                "metadata": {
                    "export_time": datetime.now().isoformat(),
                    "customer_id": customer_id,
                    "export_type": "pollution_types",
                    "version": "1.0"
                },
                "data": []
            }

            for ptype in types:
                type_data = {
                    "id": ptype.get("id"),
                    "customer_id": ptype.get("customer_id", customer_id),
                    "code": ptype.get("code"),
                    "name": ptype.get("name"),
                    "parent_code": ptype.get("parent_code"),
                    "level": ptype.get("level"),
                    "tag_name": ptype.get("tag_name"),
                    "tag_category": ptype.get("tag_category"),
                    "usage_scope": ptype.get("usage_scope"),
                    "exclusion_scope": ptype.get("exclusion_scope"),
                    "display_name": ptype.get("display_name"),
                    "parent_tag_code": ptype.get("parent_tag_code"),
                    "sort_order": ptype.get("sort_order"),
                    "description": ptype.get("description"),
                    "is_active": ptype.get("is_active", True),
                    "is_system": ptype.get("is_system", False),
                    "created_at": ptype.get("created_at"),
                    "updated_at": ptype.get("updated_at")
                }
                export_data["data"].append(type_data)

            # 确保目录存在
            os.makedirs(os.path.dirname(file_path), exist_ok=True)

            # 写入JSON文件
            with open(file_path, 'w', encoding='utf-8') as f:
                json.dump(export_data, f, ensure_ascii=False, indent=2)

            logger.info(f"已成功导出 {len(export_data['data'])} 条污染类型记录到 {file_path}")
            return len(export_data["data"])

        except Exception as e:
            logger.error(f"导出污染类型数据失败: {str(e)}", exc_info=True)
            raise

    def export_examples_to_json(self, file_path: str, customer_id: str = "default", pollution_type_id: Optional[int] = None) -> int:
        """
        导出污染类型样例数据到JSON文件

        Args:
            file_path: JSON文件路径
            customer_id: 客户ID，默认为"default"
            pollution_type_id: 污染类型ID，None表示导出所有样例

        Returns:
            导出的记录数量
        """
        try:
            logger.info(f"开始导出污染类型样例数据到JSON文件: {file_path}")

            # 获取所有样例数据（包括非激活的）
            examples = pollution_type_service.get_pollution_type_examples(customer_id, pollution_type_id, active_only=False)

            # 获取污染类型名称映射
            type_names = {}
            if examples:
                type_ids = [ex["pollution_type_id"] for ex in examples if ex.get("pollution_type_id")]
                if type_ids:
                    with get_db_session() as db:
                        types = db.query(PollutionType).filter(
                            PollutionType.id.in_(type_ids),
                            PollutionType.customer_id == customer_id
                        ).all()
                        type_names = {ptype.id: ptype.name for ptype in types}

            # 转换为可序列化的格式
            export_data = {
                "metadata": {
                    "export_time": datetime.now().isoformat(),
                    "customer_id": customer_id,
                    "pollution_type_id": pollution_type_id,
                    "export_type": "pollution_type_examples",
                    "version": "1.0"
                },
                "data": []
            }

            for example in examples:
                example_data = {
                    "id": example.get("id"),
                    "customer_id": example.get("customer_id", customer_id),
                    "pollution_type_id": example.get("pollution_type_id"),
                    "pollution_type_name": type_names.get(example.get("pollution_type_id")),
                    "example_content": example.get("example_content"),
                    "judgment_basis": example.get("judgment_basis"),
                    "keywords": example.get("keywords"),
                    "confidence_level": example.get("confidence_level"),
                    "is_active": example.get("is_active", True),
                    "created_at": example.get("created_at"),
                    "updated_at": example.get("updated_at")
                }
                export_data["data"].append(example_data)

            # 确保目录存在
            os.makedirs(os.path.dirname(file_path), exist_ok=True)

            # 写入JSON文件
            with open(file_path, 'w', encoding='utf-8') as f:
                json.dump(export_data, f, ensure_ascii=False, indent=2)

            logger.info(f"已成功导出 {len(export_data['data'])} 条污染类型样例记录到 {file_path}")
            return len(export_data["data"])

        except Exception as e:
            logger.error(f"导出污染类型样例数据失败: {str(e)}", exc_info=True)
            raise

    def export_complete_data_to_json(self, file_path: str, customer_id: str = "default") -> Dict[str, int]:
        """
        导出完整的污染类型数据（包括样例）到JSON文件

        Args:
            file_path: JSON文件路径
            customer_id: 客户ID，默认为"default"

        Returns:
            导出记录数量的字典 {"types": 数量, "examples": 数量}
        """
        try:
            logger.info(f"开始导出完整的污染类型数据到JSON文件: {file_path}")

            # 导出污染类型数据
            types_data = {
                "metadata": {
                    "export_time": datetime.now().isoformat(),
                    "customer_id": customer_id,
                    "export_type": "complete_pollution_types",
                    "version": "1.0"
                },
                "pollution_types": [],
                "pollution_type_examples": []
            }

            # 导出污染类型
            types = pollution_type_service.get_all_pollution_types(customer_id, active_only=False)
            for ptype in types:
                type_data = {
                    "id": ptype.get("id"),
                    "customer_id": ptype.get("customer_id", customer_id),
                    "code": ptype.get("code"),
                    "name": ptype.get("name"),
                    "parent_code": ptype.get("parent_code"),
                    "level": ptype.get("level"),
                    "tag_name": ptype.get("tag_name"),
                    "tag_category": ptype.get("tag_category"),
                    "usage_scope": ptype.get("usage_scope"),
                    "exclusion_scope": ptype.get("exclusion_scope"),
                    "display_name": ptype.get("display_name"),
                    "parent_tag_code": ptype.get("parent_tag_code"),
                    "sort_order": ptype.get("sort_order"),
                    "description": ptype.get("description"),
                    "is_active": ptype.get("is_active", True),
                    "is_system": ptype.get("is_system", False),
                    "created_at": ptype.get("created_at"),
                    "updated_at": ptype.get("updated_at")
                }
                types_data["pollution_types"].append(type_data)

            # 导出样例数据
            examples = pollution_type_service.get_pollution_type_examples(customer_id, None, active_only=False)
            for example in examples:
                example_data = {
                    "id": example.get("id"),
                    "customer_id": example.get("customer_id", customer_id),
                    "pollution_type_id": example.get("pollution_type_id"),
                    "example_content": example.get("example_content"),
                    "judgment_basis": example.get("judgment_basis"),
                    "keywords": example.get("keywords"),
                    "confidence_level": example.get("confidence_level"),
                    "is_active": example.get("is_active", True),
                    "created_at": example.get("created_at"),
                    "updated_at": example.get("updated_at")
                }
                types_data["pollution_type_examples"].append(example_data)

            # 确保目录存在
            os.makedirs(os.path.dirname(file_path), exist_ok=True)

            # 写入JSON文件
            with open(file_path, 'w', encoding='utf-8') as f:
                json.dump(types_data, f, ensure_ascii=False, indent=2)

            result = {
                "types": len(types_data["pollution_types"]),
                "examples": len(types_data["pollution_type_examples"])
            }

            logger.info(f"已成功导出完整的污染类型数据到 {file_path}: {result}")
            return result

        except Exception as e:
            logger.error(f"导出完整污染类型数据失败: {str(e)}", exc_info=True)
            raise

    def import_pollution_types_from_json(self, file_path: str, customer_id: str = "default", clear_existing: bool = False) -> Dict[str, Any]:
        """
        从JSON文件导入污染类型数据

        Args:
            file_path: JSON文件路径
            customer_id: 客户ID，默认为"default"
            clear_existing: 是否清空现有数据，默认为False

        Returns:
            导入结果字典
        """
        try:
            logger.info(f"开始从JSON文件导入污染类型数据: {file_path}")

            # 检查文件是否存在
            if not os.path.exists(file_path):
                raise FileNotFoundError(f"JSON文件不存在: {file_path}")

            # 读取JSON文件
            with open(file_path, 'r', encoding='utf-8') as f:
                data = json.load(f)

            # 验证数据格式
            if "data" not in data:
                raise ValueError("JSON文件格式错误：缺少data字段")

            # 如果需要清空现有数据
            if clear_existing:
                with get_db_session() as db:
                    deleted_count = db.execute(text(
                        "DELETE FROM pollution_types WHERE customer_id = :customer_id"
                    ), {"customer_id": customer_id}).rowcount
                    db.commit()
                    logger.info(f"已清空现有污染类型数据: {deleted_count} 条")

            success_count = 0
            error_count = 0
            errors = []

            # 构建code到id的映射，用于处理parent_code引用
            code_to_id_map = {}

            # 先处理没有父级引用的记录
            for item in data["data"]:
                try:
                    if not item.get("parent_code"):
                        result = self._import_single_pollution_type(item, customer_id, code_to_id_map)
                        if result:
                            success_count += 1
                        else:
                            error_count += 1
                            errors.append(f"导入失败: {item.get('name', '未知')}")
                except Exception as e:
                    error_msg = f"导入 {item.get('name', '未知')} 失败: {str(e)}"
                    errors.append(error_msg)
                    error_count += 1
                    logger.warning(error_msg, exc_info=True)

            # 然后处理有父级引用的记录
            for item in data["data"]:
                try:
                    if item.get("parent_code"):
                        result = self._import_single_pollution_type(item, customer_id, code_to_id_map)
                        if result:
                            success_count += 1
                        else:
                            error_count += 1
                            errors.append(f"导入失败: {item.get('name', '未知')}")
                except Exception as e:
                    error_msg = f"导入 {item.get('name', '未知')} 失败: {str(e)}"
                    errors.append(error_msg)
                    error_count += 1
                    logger.warning(error_msg, exc_info=True)

            result = {
                "success_count": success_count,
                "error_count": error_count,
                "errors": errors
            }

            logger.info(f"污染类型数据导入完成: 成功 {success_count} 条, 失败 {error_count} 条")
            return result

        except Exception as e:
            logger.error(f"从JSON文件导入污染类型数据失败: {str(e)}", exc_info=True)
            raise

    def import_examples_from_json(self, file_path: str, customer_id: str = "default", clear_existing: bool = False) -> Dict[str, Any]:
        """
        从JSON文件导入污染类型样例数据

        Args:
            file_path: JSON文件路径
            customer_id: 客户ID，默认为"default"
            clear_existing: 是否清空现有数据，默认为False

        Returns:
            导入结果字典
        """
        try:
            logger.info(f"开始从JSON文件导入污染类型样例数据: {file_path}")

            # 检查文件是否存在
            if not os.path.exists(file_path):
                raise FileNotFoundError(f"JSON文件不存在: {file_path}")

            # 读取JSON文件
            with open(file_path, 'r', encoding='utf-8') as f:
                data = json.load(f)

            # 验证数据格式
            if "data" not in data:
                raise ValueError("JSON文件格式错误：缺少data字段")

            # 如果需要清空现有数据
            if clear_existing:
                with get_db_session() as db:
                    deleted_count = db.execute(text(
                        "DELETE FROM pollution_type_examples WHERE customer_id = :customer_id"
                    ), {"customer_id": customer_id}).rowcount
                    db.commit()
                    logger.info(f"已清空现有污染类型样例数据: {deleted_count} 条")

            success_count = 0
            error_count = 0
            errors = []

            # 获取污染类型ID映射
            type_name_to_id = {}
            with get_db_session() as db:
                types = db.query(PollutionType).filter(
                    PollutionType.customer_id == customer_id,
                    PollutionType.is_active == True
                ).all()
                type_name_to_id = {ptype.name: ptype.id for ptype in types}

            for item in data["data"]:
                try:
                    # 处理污染类型ID引用
                    pollution_type_id = item.get("pollution_type_id")
                    if not pollution_type_id and item.get("pollution_type_name"):
                        pollution_type_id = type_name_to_id.get(item["pollution_type_name"])

                    if not pollution_type_id:
                        raise ValueError("无法确定污染类型ID")

                    result = self._import_single_pollution_example(item, customer_id, pollution_type_id)
                    if result:
                        success_count += 1
                    else:
                        error_count += 1
                        errors.append(f"导入失败: {item.get('example_content', '未知')[:50]}...")
                except Exception as e:
                    error_msg = f"导入样例失败: {item.get('example_content', '未知')[:50]}... - {str(e)}"
                    errors.append(error_msg)
                    error_count += 1
                    logger.warning(error_msg, exc_info=True)

            result = {
                "success_count": success_count,
                "error_count": error_count,
                "errors": errors
            }

            logger.info(f"污染类型样例数据导入完成: 成功 {success_count} 条, 失败 {error_count} 条")
            return result

        except Exception as e:
            logger.error(f"从JSON文件导入污染类型样例数据失败: {str(e)}", exc_info=True)
            raise

    def import_complete_data_from_json(self, file_path: str, customer_id: str = "default", clear_existing: bool = False) -> Dict[str, Any]:
        """
        从JSON文件导入完整的污染类型数据（包括样例）

        Args:
            file_path: JSON文件路径
            customer_id: 客户ID，默认为"default"
            clear_existing: 是否清空现有数据，默认为False

        Returns:
            导入结果字典
        """
        try:
            logger.info(f"开始从JSON文件导入完整的污染类型数据: {file_path}")

            # 检查文件是否存在
            if not os.path.exists(file_path):
                raise FileNotFoundError(f"JSON文件不存在: {file_path}")

            # 读取JSON文件
            with open(file_path, 'r', encoding='utf-8') as f:
                data = json.load(f)

            # 验证数据格式
            if "pollution_types" not in data or "pollution_type_examples" not in data:
                raise ValueError("JSON文件格式错误：缺少pollution_types或pollution_type_examples字段")

            # 如果需要清空现有数据
            if clear_existing:
                with get_db_session() as db:
                    deleted_types = db.execute(text(
                        "DELETE FROM pollution_types WHERE customer_id = :customer_id"
                    ), {"customer_id": customer_id}).rowcount
                    deleted_examples = db.execute(text(
                        "DELETE FROM pollution_type_examples WHERE customer_id = :customer_id"
                    ), {"customer_id": customer_id}).rowcount
                    db.commit()
                    logger.info(f"已清空现有数据: {deleted_types} 个污染类型, {deleted_examples} 个样例")

            # 先导入污染类型
            types_result = self._import_pollution_types_from_data(data["pollution_types"], customer_id)
            logger.info(f"污染类型导入完成: {types_result}")

            # 再导入样例
            examples_result = self._import_examples_from_data(data["pollution_type_examples"], customer_id)
            logger.info(f"污染类型样例导入完成: {examples_result}")

            result = {
                "types": types_result,
                "examples": examples_result,
                "total_success": types_result["success_count"] + examples_result["success_count"],
                "total_errors": types_result["error_count"] + examples_result["error_count"]
            }

            logger.info(f"完整数据导入完成: 总计成功 {result['total_success']} 条, 失败 {result['total_errors']} 条")
            return result

        except Exception as e:
            logger.error(f"从JSON文件导入完整污染类型数据失败: {str(e)}", exc_info=True)
            raise

    def _import_single_pollution_type(self, item: Dict[str, Any], customer_id: str, code_to_id_map: Dict[str, int]) -> bool:
        """
        导入单个污染类型

        Args:
            item: 污染类型数据
            customer_id: 客户ID
            code_to_id_map: code到id的映射

        Returns:
            是否成功
        """
        try:
            # 验证必填字段
            if not item.get("code") or not item.get("name"):
                raise ValueError("污染类型代码和名称不能为空")

            with get_db_session() as db:
                # 检查是否已存在
                existing = db.execute(text(
                    "SELECT id FROM pollution_types WHERE customer_id = :customer_id AND code = :code"
                ), {"customer_id": customer_id, "code": item["code"]}).fetchone()

                if existing:
                    logger.info(f"跳过已存在的污染类型: {item['name']} ({item['code']})")
                    return True

                # 处理parent_code
                parent_code = None
                if item.get("parent_code"):
                    if item["parent_code"] in code_to_id_map:
                        parent_code = item["parent_code"]
                    else:
                        # 尝试从数据库查找
                        parent = db.execute(text(
                            "SELECT code FROM pollution_types WHERE customer_id = :customer_id AND code = :parent_code"
                        ), {"customer_id": customer_id, "parent_code": item["parent_code"]}).fetchone()
                        if parent:
                            parent_code = parent[0]
                        else:
                            logger.warning(f"未找到父级污染类型: {item['parent_code']}")

                # 插入新记录
                insert_sql = """
                INSERT INTO pollution_types (
                    customer_id, code, name, parent_code, level,
                    tag_name, tag_category, usage_scope, exclusion_scope,
                    display_name, parent_tag_code, sort_order, description,
                    is_active, is_system, created_at, updated_at
                ) VALUES (
                    :customer_id, :code, :name, :parent_code, :level,
                    :tag_name, :tag_category, :usage_scope, :exclusion_scope,
                    :display_name, :parent_tag_code, :sort_order, :description,
                    :is_active, :is_system, :created_at, :updated_at
                )
                """

                params = {
                    "customer_id": customer_id,
                    "code": item["code"],
                    "name": item["name"],
                    "parent_code": parent_code,
                    "level": item.get("level", 1),
                    "tag_name": item.get("tag_name"),
                    "tag_category": item.get("tag_category"),
                    "usage_scope": item.get("usage_scope"),
                    "exclusion_scope": item.get("exclusion_scope"),
                    "display_name": item.get("display_name"),
                    "parent_tag_code": item.get("parent_tag_code"),
                    "sort_order": item.get("sort_order", 0),
                    "description": item.get("description"),
                    "is_active": item.get("is_active", True),
                    "is_system": item.get("is_system", False),
                    "created_at": item.get("created_at", datetime.now().isoformat()),
                    "updated_at": item.get("updated_at", datetime.now().isoformat())
                }

                result = db.execute(text(insert_sql), params)
                db.commit()

                # 更新映射
                new_id = result.lastrowid
                code_to_id_map[item["code"]] = new_id

                logger.info(f"成功导入污染类型: {item['name']} ({item['code']})")
                return True

        except Exception as e:
            logger.error(f"导入单个污染类型失败: {str(e)}", exc_info=True)
            return False

    def _import_single_pollution_example(self, item: Dict[str, Any], customer_id: str, pollution_type_id: int) -> bool:
        """
        导入单个污染类型样例

        Args:
            item: 样例数据
            customer_id: 客户ID
            pollution_type_id: 污染类型ID

        Returns:
            是否成功
        """
        try:
            # 验证必填字段
            if not item.get("example_content"):
                raise ValueError("样例内容不能为空")

            with get_db_session() as db:
                # 检查是否已存在
                existing = db.execute(text(
                    "SELECT id FROM pollution_type_examples WHERE customer_id = :customer_id AND pollution_type_id = :pollution_type_id AND example_content = :example_content"
                ), {
                    "customer_id": customer_id,
                    "pollution_type_id": pollution_type_id,
                    "example_content": item["example_content"]
                }).fetchone()

                if existing:
                    logger.info(f"跳过已存在的样例: {item['example_content'][:50]}...")
                    return True

                # 插入新记录
                insert_sql = """
                INSERT INTO pollution_type_examples (
                    customer_id, pollution_type_id, example_content, judgment_basis,
                    keywords, confidence_level, is_active, created_at, updated_at
                ) VALUES (
                    :customer_id, :pollution_type_id, :example_content, :judgment_basis,
                    :keywords, :confidence_level, :is_active, :created_at, :updated_at
                )
                """

                params = {
                    "customer_id": customer_id,
                    "pollution_type_id": pollution_type_id,
                    "example_content": item["example_content"],
                    "judgment_basis": item.get("judgment_basis"),
                    "keywords": self._format_keywords(item.get("keywords")),
                    "confidence_level": item.get("confidence_level", 0.8),
                    "is_active": item.get("is_active", True),
                    "created_at": item.get("created_at", datetime.now().isoformat()),
                    "updated_at": item.get("updated_at", datetime.now().isoformat())
                }

                db.execute(text(insert_sql), params)
                db.commit()

                logger.info(f"成功导入样例: {item['example_content'][:50]}...")
                return True

        except Exception as e:
            logger.error(f"导入单个污染类型样例失败: {str(e)}", exc_info=True)
            return False

    def _import_pollution_types_from_data(self, types_data: List[Dict], customer_id: str) -> Dict[str, Any]:
        """
        从数据列表导入污染类型

        Args:
            types_data: 污染类型数据列表
            customer_id: 客户ID

        Returns:
            导入结果
        """
        success_count = 0
        error_count = 0
        errors = []
        code_to_id_map = {}

        # 先处理没有父级引用的记录
        for item in types_data:
            try:
                if not item.get("parent_code"):
                    result = self._import_single_pollution_type(item, customer_id, code_to_id_map)
                    if result:
                        success_count += 1
                    else:
                        error_count += 1
                        errors.append(f"导入失败: {item.get('name', '未知')}")
            except Exception as e:
                error_msg = f"导入 {item.get('name', '未知')} 失败: {str(e)}"
                errors.append(error_msg)
                error_count += 1
                logger.warning(error_msg, exc_info=True)

        # 然后处理有父级引用的记录
        for item in types_data:
            try:
                if item.get("parent_code"):
                    result = self._import_single_pollution_type(item, customer_id, code_to_id_map)
                    if result:
                        success_count += 1
                    else:
                        error_count += 1
                        errors.append(f"导入失败: {item.get('name', '未知')}")
            except Exception as e:
                error_msg = f"导入 {item.get('name', '未知')} 失败: {str(e)}"
                errors.append(error_msg)
                error_count += 1
                logger.warning(error_msg, exc_info=True)

        return {
            "success_count": success_count,
            "error_count": error_count,
            "errors": errors
        }

    def _import_examples_from_data(self, examples_data: List[Dict], customer_id: str) -> Dict[str, Any]:
        """
        从数据列表导入污染类型样例

        Args:
            examples_data: 样例数据列表
            customer_id: 客户ID

        Returns:
            导入结果
        """
        success_count = 0
        error_count = 0
        errors = []

        # 获取污染类型ID映射
        type_name_to_id = {}
        with get_db_session() as db:
            types = db.query(PollutionType).filter(
                PollutionType.customer_id == customer_id,
                PollutionType.is_active == True
            ).all()
            type_name_to_id = {ptype.name: ptype.id for ptype in types}

        for item in examples_data:
            try:
                # 处理污染类型ID引用
                pollution_type_id = item.get("pollution_type_id")
                if not pollution_type_id and item.get("pollution_type_name"):
                    pollution_type_id = type_name_to_id.get(item["pollution_type_name"])

                if not pollution_type_id:
                    raise ValueError("无法确定污染类型ID")

                result = self._import_single_pollution_example(item, customer_id, pollution_type_id)
                if result:
                    success_count += 1
                else:
                    error_count += 1
                    errors.append(f"导入失败: {item.get('example_content', '未知')[:50]}...")
            except Exception as e:
                error_msg = f"导入样例失败: {item.get('example_content', '未知')[:50]}... - {str(e)}"
                errors.append(error_msg)
                error_count += 1
                logger.warning(error_msg, exc_info=True)

        return {
            "success_count": success_count,
            "error_count": error_count,
            "errors": errors
        }

    def _format_keywords(self, keywords) -> str:
        """
        格式化关键词字段

        Args:
            keywords: 关键词数据（可能是字符串、列表或其他类型）

        Returns:
            格式化后的字符串
        """
        if keywords is None:
            return None

        if isinstance(keywords, str):
            return keywords

        if isinstance(keywords, (list, tuple)):
            # 将列表转换为逗号分隔的字符串
            filtered_keywords = [str(k).strip() for k in keywords if k and str(k).strip()]
            return ",".join(filtered_keywords) if filtered_keywords else None

        # 其他类型转换为字符串
        return str(keywords) if keywords else None


# 全局服务实例
pollution_type_import_export = PollutionTypeImportExport()